package com.hydx.vat.dao;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.hydx.vat.entity.Commodity;

public interface CommodityDao extends JpaRepository<Commodity, Integer> {

	// 获取总记录通过时间区间和状态
	@Query("select u from Commodity u where u.type = :commodityType and( u.inCompanyId=:companyId or u.outCompanyId=:companyId )and (u.vatDate >= :startDate and u.vatDate <= :endDate) and u.vatDate like :date%")
	Page<Commodity> getByCommodityData(Pageable pageable, @Param("commodityType") Integer commodityType,
			@Param("companyId") Integer companyId, @Param("startDate") String startDate,
			@Param("endDate") String endDate, @Param("date") String date);

	
	@Query("select u from Commodity u where u.vatNumber like ?1%")
	Page<Commodity> getCommodityByNum(Pageable pageable, String vatNumber);

	
	// 获取总记录通过时间区间
	@Query("select u from Commodity u where ( u.inCompanyId=:companyId or u.outCompanyId=:companyId )and (u.vatDate >= :startDate and u.vatDate <= :endDate) and u.vatDate like :date%")
	Page<Commodity> getByCommodityData(Pageable pageable, @Param("companyId") Integer companyId,
			@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("date") String date);

	// 获取总记录通过具体时间
	@Query("select u from Commodity u where u.type = :commodityType and ( u.inCompanyId=:companyId or u.outCompanyId=:companyId )and (u.vatDate >= :startDate and u.vatDate <= :endDate)")
	Page<Commodity> getByCommodityData(Pageable pageable, @Param("commodityType") Integer commodityType,
			@Param("companyId") Integer companyId, @Param("startDate") String startDate,
			@Param("endDate") String endDate);

	// 获取总记录通过具体年份
	@Query("select u from Commodity u where u.type = :commodityType and u.vatDate like :date% and (u.inCompanyId=:companyId or u.outCompanyId=:companyId )")
	Page<Commodity> getByCommodityType(Pageable pageable, @Param("commodityType") Integer commodityType,
			@Param("companyId") Integer companyId, @Param("date") String date);

	// 获取总记录通过状态
	@Query("select u from Commodity u where u.type = :commodityType and u.inCompanyId=:companyId or u.outCompanyId=:companyId")
	Page<Commodity> getByCommodityType(Pageable pageable, @Param("commodityType") Integer commodityType,
			@Param("companyId") Integer companyId);

	// 获取总记录通过公司id
	@Query("select u from Commodity u where(u.inCompanyId=:companyId or u.outCompanyId=:companyId)")
	Page<Commodity> getAllCommodity(Pageable pageable, @Param("companyId") Integer companyId);

	// 获取总记录
	@Query("select u from Commodity u where(u.inCompanyId=:companyId or u.outCompanyId=:companyId)and u.vatDate like :date%")
	Page<Commodity> getAllCommodity(Pageable pageable, @Param("companyId") Integer companyId,
			@Param("date") String date);

	// 获取所有通过发票id获取详细
	Commodity findByVatNumber(String numble);

	// 获取所有记录
	@Query("select u from Commodity u where  u.vatDate like :date% and (u.inCompanyId=:companyId or u.outCompanyId=:companyId)")
	Page<Commodity> getAll(Pageable pageable, @Param("companyId") Integer companyId, @Param("date") String date);

	// 获取所有记录
	@Query("select u from Commodity u where u.inCompanyId=:companyId or u.outCompanyId=:companyId")
	Page<Commodity> getAll(Pageable pageable, @Param("companyId") Integer companyId);

	// 获取所有记录年度
	@Query("select u.commodityId from Commodity u where  u.inCompanyId=:companyId and u.vatDate like :date%")
	List<Integer> getGoodAllComodityYear(@Param("companyId") Integer companyId, @Param("date") String date);

	// 获取所有销售记录
	@Query("select u.commodityId from Commodity u where  u.inCompanyId=:companyId")
	List<Integer> getGoodAllComodityYear(@Param("companyId") Integer companyId);

	// 获取所有进货记录
	@Query("select u.commodityId from Commodity u where  u.outCompanyId=:companyId")
	List<Integer> getOutGoodAllComodityYear(@Param("companyId") Integer companyId);

	// 获取所有记录季度
	@Query(value = "select commodityId from commodity  where  inCompanyId=:companyId and (CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))) like :date%", nativeQuery = true)
	List<Integer> getGoodAllComodityQuter(@Param("companyId") Integer companyId, @Param("date") String date);

	// 获取所有记录月度
	@Query(value = "select commodityId from commodity  where  inCompanyId=:companyId and (DATE_FORMAT(vatDate,'%Y-%m')) like :date%", nativeQuery = true)
	List<Integer> getGoodAllComodityMonth(@Param("companyId") Integer companyId, @Param("date") String date);

	// 获取所有记录
	@Query("select u.commodityId from Commodity u where  u.outCompanyId=:companyId and u.vatDate like :date%")
	List<Integer> getInGoodAllComodity(@Param("companyId") Integer companyId, @Param("date") String date);

	// 获取出项金额通过时间
	@Query("SELECT SUM(u.allPrice) FROM Commodity u where u.inCompanyId=:companyId and u.vatDate like :date%")
	Double getAllInPrice(@Param("companyId") Integer companyId, @Param("date") String date);

	// 获取出项金额
	@Query("SELECT SUM(u.allPrice) FROM Commodity u where u.inCompanyId=:companyId ")
	Double getAllInPrice(@Param("companyId") Integer companyId);

	// 获取进项金额通过时间
	@Query("SELECT SUM(u.allPrice) FROM Commodity u where u.outCompanyId=:companyId and u.vatDate like :date%")
	Double getAllOutPrice(@Param("companyId") Integer companyId, @Param("date") String date);

	// 获取进项金额
	@Query("SELECT SUM(u.allPrice) FROM Commodity u where u.outCompanyId=:companyId")
	Double getAllOutPrice(@Param("companyId") Integer companyId);

	// 获取纳税金额通过时间
	@Query("SELECT SUM(u.vatMoney) FROM Commodity u where u.outCompanyId=:companyId or u.inCompanyId=:companyId and u.vatDate like :date%")
	Double getAllVatMoney(@Param("companyId") Integer companyId, @Param("date") String date);

	// 获取纳税金额
	@Query("SELECT SUM(u.vatMoney) FROM Commodity u where u.outCompanyId=:companyId or u.inCompanyId=:companyId")
	Double getAllVatMoney(@Param("companyId") Integer companyId);

	// 获取所有记录的年份
	@Query(value = "SELECT YEAR(vatDate) FROM commodity GROUP BY YEAR(vatDate)", nativeQuery = true)
	List<Integer> getAllYear();

	// 获取获取公司年度总销售额
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=?1 GROUP BY DATE_FORMAT(vatDate,'%Y')", nativeQuery = true)
	List<Double> getInTurnoverByCompanyId(Integer companyId);

	// 获取获取公司年度总销售额通过时间
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=?1 and DATE_FORMAT(vatDate,'%Y') like ?2%", nativeQuery = true)
	List<Double> getInTurnoverByCompanyId(Integer companyId, Integer year);

	// 获取获取公司季度度总销售额
	@Query(value = "SELECT SUM(allPrice)FROM commodity tmptable WHERE inCompanyId=?1 GROUP BY CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))", nativeQuery = true)
	List<Double> getInTurnoverByQuarter(Integer companyId);

	// 获取获取公司季度度总销售额
	@Query(value = "SELECT SUM(allPrice)FROM commodity tmptable WHERE inCompanyId=?1 and ((CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3)))like ?2%)GROUP BY CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))", nativeQuery = true)
	List<Double> getInTurnoverByQuarter(Integer companyId, Integer year);

	// 获取获取公司年度进货的成本季度
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE outCompanyId=?1 GROUP BY CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))", nativeQuery = true)
	List<Double> getOutTurnoverByQuarter(Integer companyId);

	// 获取获取公司季度
	@Query(value = "SELECT CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3)) FROM commodity tmptable WHERE inCompanyId=?1 GROUP BY CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))", nativeQuery = true)
	List<String> getQuarter(Integer companyId);

	// 获取获取公司季度通过具体的时间
	@Query(value = "SELECT CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3)) FROM commodity tmptable WHERE inCompanyId=?1 and DATE_FORMAT(vatDate,'%Y') like ?2% GROUP BY CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))", nativeQuery = true)
	List<String> getQuarter(Integer companyId, Integer year);

	// 获取获取公司月份
	@Query(value = "SELECT DATE_FORMAT(vatDate,'%Y-%m') FROM commodity tmptable WHERE inCompanyId=?1 GROUP BY DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<String> getMonthBycompanyId(Integer companyId);

	// 获取获取公司月度进货的总销售额
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=:companyId  GROUP BY DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<Double> getInTurnoverByMonth(@Param("companyId") Integer companyId);

	// 获取获取公司月度进货的总销售额
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=:companyId  and DATE_FORMAT(vatDate,'%Y-%m') like :year% GROUP BY  DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<Double> getInTurnoverByMonth(@Param("companyId") Integer companyId, @Param("year") Integer year);

	// 获取获取公司月度进货的总成本
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE outCompanyId=:companyId  GROUP BY DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<Double> getOutTurnoverByMonth(@Param("companyId") Integer companyId);

	// 获取获取公司年度进货的成本
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE outCompanyId=?1 GROUP BY DATE_FORMAT(vatDate,'%Y')", nativeQuery = true)
	List<Double> getOutTurnoverByCompanyId(Integer companyId);

	// 客户对营业额的贡献占比
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=?1 and outCompanyId=?2 GROUP BY DATE_FORMAT(vatDate,'%Y')", nativeQuery = true)
	List<Double> getOutProportionByCompanyId(Integer inCompanyId, Integer outCompanyId);

	// 客户对营业额的贡献占比
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=?1 and outCompanyId=?2 and DATE_FORMAT(vatDate,'%Y') like ?3%", nativeQuery = true)
	List<Double> getOutProportionByCompanyId(Integer inCompanyId, Integer outCompanyId, Integer year);

	// 客户对营业额的贡献占比按照季度分
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=?1 and outCompanyId=?2 and ((CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3)))like ?3%)", nativeQuery = true)
	List<Double> getOutProportionByQuarter(Integer inCompanyId, Integer outCompanyId, String year);

	// 客户对营业额的贡献占比按月份分
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=?1 and outCompanyId=?2 and DATE_FORMAT(vatDate,'%Y-%m') like ?3%", nativeQuery = true)
	List<Double> getOutProportionByMonth(Integer inCompanyId, Integer outCompanyId, String year);

	// 有史以来供货商对进货总价的占比
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE outCompanyId=?1 and inCompanyId=?2 GROUP BY DATE_FORMAT(vatDate,'%Y')", nativeQuery = true)
	List<Double> getInProportionByCompanyId(Integer inCompanyId, Integer outCompanyId);

	// 具体年份以来供货商对进货总价的占比
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE outCompanyId=?1 and inCompanyId=?2 and DATE_FORMAT(vatDate,'%Y') like ?3%", nativeQuery = true)
	List<Double> getInProportionByCompanyId(Integer inCompanyId, Integer outCompanyId, Integer year);

	// 具体年份以来供货商对进货总价的占比季度
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE outCompanyId=?1 and inCompanyId=?2 and ((CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3)))like ?3%)", nativeQuery = true)
	List<Double> getInProportionByQuarter(Integer inCompanyId, Integer outCompanyId, String year);

	// 具体年份以来供货商对进货总价的占比月份
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE outCompanyId=?1 and inCompanyId=?2 and DATE_FORMAT(vatDate,'%Y-%m') like ?3%", nativeQuery = true)
	List<Double> getInProportionByMonth(Integer inCompanyId, Integer outCompanyId, String year);

	// 获取获取公司月度进货的总成本
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE inCompanyId=:companyId AND vatDate LIKE :year% GROUP BY DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<Double> getInTurnoverByMonth(@Param("companyId") Integer companyId, @Param("year") String year);

	// 获取获取公司月度进货的总销售额
	@Query(value = "SELECT SUM(allPrice) FROM commodity tmptable WHERE outCompanyId=:companyId AND vatDate LIKE :year% GROUP BY DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<Double> getOutTurnoverByMonth(@Param("companyId") Integer companyId, @Param("year") String year);

	// 获取获取公司年度纳税额度
	@Query(value = "SELECT SUM(vatMoney) FROM commodity tmptable WHERE (inCompanyId=?1 ) GROUP BY DATE_FORMAT(vatDate,'%Y')", nativeQuery = true)
	List<Double> getAllVatByCompanyId(Integer companyId);

	// 获取获取公司年度纳税额度通过时间
	@Query(value = "SELECT SUM(vatMoney) FROM commodity tmptable WHERE (inCompanyId=?1 ) and DATE_FORMAT(vatDate,'%Y')like ?2%", nativeQuery = true)
	List<Double> getAllVatByCompanyId(Integer companyId, Integer year);

	// 获取获取公司月度纳税额度
	@Query(value = "SELECT SUM(vatMoney) FROM commodity tmptable WHERE (outCompanyId=:companyId or inCompanyId=:companyId) AND vatDate LIKE :year% GROUP BY DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<Double> getVatByMonth(@Param("companyId") Integer companyId, @Param("year") String year);

	// 获取获取公司月度纳税额度
	@Query(value = "SELECT SUM(vatMoney) FROM commodity tmptable WHERE (outCompanyId=:companyId or inCompanyId=:companyId) GROUP BY DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<Double> getVatByMonth(@Param("companyId") Integer companyId);

	// 获取获取公司月度纳税额度
	@Query(value = "SELECT SUM(vatMoney) FROM commodity tmptable WHERE (outCompanyId=:companyId or inCompanyId=:companyId) and DATE_FORMAT(vatDate,'%Y-%m')like :year% GROUP BY DATE_FORMAT(vatDate,'%Y-%m')", nativeQuery = true)
	List<Double> getVatByMonth(@Param("companyId") Integer companyId, @Param("year") Integer year);

	// 获取获取公司季度纳税额度
	@Query(value = "SELECT SUM(vatMoney) FROM commodity tmptable WHERE (outCompanyId=:companyId or inCompanyId=:companyId)  GROUP BY CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))", nativeQuery = true)
	List<Double> getVatByQuarter(@Param("companyId") Integer companyId);

	// 获取获取公司季度纳税额度
	@Query(value = "SELECT SUM(vatMoney) FROM commodity tmptable WHERE (outCompanyId=:companyId or inCompanyId=:companyId) and (CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))) like :year% GROUP BY CONCAT(DATE_FORMAT(vatDate, '%Y '),FLOOR((DATE_FORMAT(vatDate, '%m ')+2)/3))", nativeQuery = true)
	List<Double> getVatByQuarter(@Param("companyId") Integer companyId, @Param("year") Integer year);

	// 获取获取该年的月份
	@Query(value = "SELECT MONTH(vatDate) AS m FROM commodity AS d WHERE YEAR(vatDate)=?1 GROUP BY MONTH(vatDate)", nativeQuery = true)
	List<Integer> getMonth(String data);

	// 获取获取该年的月份
	@Query(value = "SELECT MONTH(vatDate) AS m FROM commodity AS d WHERE (outCompanyId=:companyId or inCompanyId=:companyId) and YEAR(vatDate)= :vatDate GROUP BY MONTH(vatDate)", nativeQuery = true)
	List<String> getMonthBycompanyId(@Param("companyId")Integer companyId,@Param("vatDate") String year);

	// 获取分公司的所有供货商
	@Query(value = "SELECT inCompanyId  FROM commodity  WHERE outCompanyId=?1 GROUP BY inCompanyId", nativeQuery = true)
	List<Integer> getInCompanyId(Integer companyId);

	// 获取分公司的所有客户
	@Query(value = "SELECT outCompanyId  FROM commodity  WHERE inCompanyId=?1 GROUP BY outCompanyId", nativeQuery = true)
	List<Integer> getOutCompanyId(Integer companyId);
}
